﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Data;
using School.Entities;

namespace School.Accounting
{
    public static class Expense
    {
        //commented Lines12
        private static SchoolEntities db = new SchoolEntities();
        
        public static void Add(ACC_Expense expense, string[] campuses, string[] certificates, string[] stages, string[] grades,DataTable payments = null)
        {
            expense.Group_Id = 1;
            db.AddToACC_Expense(expense);
            db.SaveChanges();            

            ApplyExpenseTo(expense.ID, campuses, certificates, stages, grades);

            if (payments != null)
                AddPayments(expense.ID, payments);
        }

        private static void AddPayments(long id,DataTable payments)
        {
            foreach (DataRow r in payments.Rows)
            {
                db.AddToACC_Expense_Payments(new ACC_Expense_Payments() { Expense_Id = id, Due_Date = DateTime.Parse(r[1].ToString()),  Value = decimal.Parse(r[2].ToString()) });
            }

            db.SaveChanges();
        }
        
        public static void Update(ACC_Expense expense, DateTime installments_due_date, string[] campuses, string[] certificates, string[] stages, string[] grades)
        {
            var entity = db.ACC_Expense.Single(x => x.ID == expense.ID);
            
            

            db.SaveChanges();

            DeleteCampuses(expense.ID);
            DeleteCertificates(expense.ID);
            DeleteStages(expense.ID);
            DeleteGrades(expense.ID);            

            ApplyExpenseTo(expense.ID, campuses, certificates, stages, grades);                   
        }

        public static bool Delete(long id)
        {
            var ex = (from x in db.ACC_Expense where x.ID == id select x).First();

            if (ex != null)
            {
                if (!CheckVouchers(id))
                {
                    DeleteCampuses(id);
                    DeleteCertificates(id);
                    DeleteGrades(id);
                    DeleteStages(id);
                    DeletePayments(id);
                    DeleteDiscounts(id);
                    DeleteScreens(id);
                    db.DeleteObject(ex);
                    db.SaveChanges();
                    return true;
                }
                else
                    return false;
            }
            else
                return false;
        }

        private static void ApplyExpenseTo(long expense, string[] campuses, string[] certificates, string[] stages, string[] grades)
        {
            if (campuses != null)
            {
                foreach (string campus in campuses)
                {
                    db.AddToACC_Expenses_Campuses(new ACC_Expenses_Campuses() { Expense = expense, Campus = int.Parse(campus) });
                }
            }

            if (certificates != null)
            {
                foreach (string certificate in certificates)
                {
                    db.AddToACC_Expenses_Certificates(new ACC_Expenses_Certificates() { Expense = expense, Certificate = int.Parse(certificate) });
                }
            }

            if (stages != null)
            {
                foreach (string stage in stages)
                {
                    db.AddToACC_Expenses_Stages(new ACC_Expenses_Stages() { Expense = expense, Stage = int.Parse(stage) });
                }
            }

            if (grades != null)
            {
                foreach (string grade in grades)
                {
                    db.AddToACC_Expenses_Grades(new ACC_Expenses_Grades() { Expense = expense, Grade = int.Parse(grade) });
                }
            }

            db.SaveChanges();
        }        

        public static IEnumerable<ACC_Expense> GetAllExpenses()
        {
            return from e in db.ACC_Expense
                   orderby e.Name
                   select e;
        }

        public static ACC_Expense GetSingleExpense(long id)
        {
            return (from x in db.ACC_Expense where x.ID == id select x).First();
        }
        
        public static IEnumerable<long> GetCampusesIds(long expense)
        {
            return from c in db.ACC_Expenses_Campuses 
                   where c.Expense == expense 
                   select c.Campus;
        }

        public static IEnumerable<long> GetCertificatesIds(long expense)
        {
            return from c in db.ACC_Expenses_Certificates
                   where c.Expense == expense
                   select c.Certificate;
        }

        public static IEnumerable<long> GetStagesIds(long expense)
        {
            return from s in db.ACC_Expenses_Stages
                   where s.Expense == expense
                   select s.Stage;
        }

        public static IEnumerable<long> GetGradesIds(long expense)
        {
            return from g in db.ACC_Expenses_Grades
                   where g.Expense == expense
                   select g.Grade;
        }

        private static void DeleteCampuses(long expense)
        {
            var rows = from c in db.ACC_Expenses_Campuses where c.Expense == expense select c;

            foreach (var row in rows)
            {
                db.DeleteObject(row);
            }

            db.SaveChanges();
        }

        private static void DeleteCertificates(long expense)
        {
            var rows = from c in db.ACC_Expenses_Certificates where c.Expense == expense select c;

            foreach (var row in rows)
            {
                db.DeleteObject(row);
            }

            db.SaveChanges();
        }

        private static void DeleteStages(long expense)
        {
            var rows = from c in db.ACC_Expenses_Stages where c.Expense == expense select c;

            foreach (var row in rows)
            {
                db.DeleteObject(row);
            }

            db.SaveChanges();
        }

        private static void DeleteGrades(long expense)
        {
            var rows = from c in db.ACC_Expenses_Grades where c.Expense == expense select c;

            foreach (var row in rows)
            {
                db.DeleteObject(row);
            }

            db.SaveChanges();
        }

        private static void DeletePayments(long expense)
        {
            var rows = from c in db.ACC_Expense_Payments where c.Expense_Id == expense select c;

            foreach (var row in rows)
            {
                db.DeleteObject(row);
            }

            db.SaveChanges();
        }

        private static void DeleteScreens(long expense)
        {
            var rows = from c in db.ACC_Expenses_Screens where c.Expense_Id == expense select c;

            foreach (var row in rows)
            {
                db.DeleteObject(row);
            }

            db.SaveChanges();
        }

        private static void DeleteDiscounts(long expense)
        {
            var rows = from c in db.ACC_Expense_Discount where c.Expense_Id == expense select c;

            foreach (var row in rows)
            {
                db.DeleteObject(row);
            }

            db.SaveChanges();
        } 

        public static IEnumerable<ORG_Department> Departments()
        {
            return from x in db.ORG_Department orderby x.Name select x;
        }

        private static bool CheckVouchers(long id)
        {
            long year = (from x in db.ORG_EductionalYear where x.IsCurrent == true select x.Id).First();

            var v = from x in db.ACC_Voucher where x.Current_Educational_Year == year && x.Is_Closed == true select x;

            if (v.Count() > 0)
                return true;
            else
                return false;
        }

        public static IEnumerable<ACC_Expense> GetALl_Expense()
        {
            return (from ex in db.ACC_Expense select ex);
        }
        
        public static IEnumerable<SEC_Screen> GetALl_Screen()
        {
            return (from ex in db.SEC_Screen select ex);
        }

        public static bool AddToExpenses_Screens(int Expense_Id, int Screen_Id)
        {
            try
            {
                ACC_Expenses_Screens Ex = new ACC_Expenses_Screens();
                Ex.Expense_Id = Expense_Id;
                Ex.Screen_Id = Screen_Id;
                db.AddToACC_Expenses_Screens(Ex);
                db.SaveChanges();
                return true;
            }
            catch (Exception)
            {
                return false;

            }

        }
        
        public static IEnumerable<ACC_Expenses_Screens> Get_Expenses_Screens()
        {
            return (from ex in db.ACC_Expenses_Screens select ex);
        }

        public static bool Delete_Expenses_Screens(int Id)
        {
            var deletequery = (from st in db.ACC_Expenses_Screens
                               where st.Id == Id
                               select st).Single();
            try
            {
                db.DeleteObject(deletequery);
                db.SaveChanges();
                return true;

            }
            catch (Exception)
            {

                return false;
            }

        }

        public static IEnumerable<ACC_Expense> GetExpensesByStudent(long studentId)
        {
            try
            {
                var student = db.TE_Student.Single(x => x.Id == studentId);

                var expenses = from x in db.ACC_Expense where x.Automatic_With_Services == false select x;

                List<ACC_Expense> ex = new List<ACC_Expense>();

                ex.AddRange(expenses.Where(x => x.ACC_Expenses_Campuses.Where(y => y.Campus == student.Campus_Id).Count() > 0));
                //ex.AddRange(expenses.Where(x => x.ACC_Expenses_Certificates.Where(y => y.ORG_Certificate.Campus_Id == student.Campus_Id).Count() > 0));
                //ex.AddRange(expenses.Where(x => x.ACC_Expenses_Stages.Where(y => y.ORG_Stage.ORG_Certificate.Campus_Id == student.Campus_Id).Count() > 0));
                //ex.AddRange(expenses.Where(x => x.ACC_Expenses_Grades.Where(y => y.ORG_Grade.ORG_Stage.ORG_Certificate.Campus_Id == student.Campus_Id).Count() > 0));

                ex.AddRange(expenses.Where(x => x.ACC_Expenses_Certificates.Where(y => y.Certificate == student.Certificate_Id).Count() > 0));
                //ex.AddRange(expenses.Where(x => x.ACC_Expenses_Stages.Where(y => y.ORG_Stage.Certificate_Id == student.Certificate_Id).Count() > 0));
                //ex.AddRange(expenses.Where(x => x.ACC_Expenses_Grades.Where(y => y.ORG_Grade.ORG_Stage.Certificate_Id == student.Certificate_Id).Count() > 0));

                ex.AddRange(expenses.Where(x => x.ACC_Expenses_Stages.Where(y => y.Stage == student.Stage_Id).Count() > 0));
                //ex.AddRange(expenses.Where(x => x.ACC_Expenses_Grades.Where(y => y.ORG_Grade.Stage_Id == student.Stage_Id).Count() > 0));

                ex.AddRange(expenses.Where(x => x.ACC_Expenses_Grades.Where(y => y.Grade == student.Grade_ID).Count() > 0));

                List<ACC_Expense> exFinal = new List<ACC_Expense>();

                foreach (var e in ex)
                {
                    long cy = (from x in db.ORG_EductionalYear where x.IsCurrent == true select x.Id).First();
                    long Expense_Id = e.ID;
                    long Student_Id = studentId;
                    var apo = from x in db.ACC_Voucher
                              where
                                  x.Student_Id == Student_Id &&
                                  x.Expense_Id == Expense_Id &&
                                  x.Current_Educational_Year == cy &&
                                  x.Is_Active == true
                              select x.Payment_Order_Number;



                    var paid = from x in db.ACC_Payment_Order where x.Student_Id == Student_Id && x.ACC_Expense_Payments.ACC_Expense.ID == Expense_Id && apo.Contains(x.Payment_Order_Number) select x.Payment_Id;

                    IEnumerable<ACC_Expense_Payments> payments;

                    if (paid.Count() > 0)
                        payments = from x in db.ACC_Expense_Payments where paid.Contains(x.Id) == false && x.Expense_Id == Expense_Id select x;
                    else
                        payments = from x in db.ACC_Expense_Payments where x.Expense_Id == Expense_Id select x;

                    if (payments.Count() > 0)
                        exFinal.Add(e);
                }

                return exFinal.Distinct();
            }
            catch (Exception)
            {
                
                return null;
            }
        }
    }
}
